﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace DataChannel.Web
{
    public class SqlService
    {
        private SqlConnection sqlConnection = null;
        private static SqlService instance = null;

        private SqlService(string sqlString)
        {

            try
            {
                sqlConnection = new SqlConnection(sqlString);
            }
            catch (Exception e)
            {
                
                throw e;
            }


        }

        ~SqlService()
        {
            if(this.sqlConnection.State != ConnectionState.Closed)
            {
                this.sqlConnection.Close();
            }
        }
        public static SqlService GetInstance(string sqlString)
        {
            if (instance == null)
            {

                instance = new SqlService(sqlString);
            }

            return instance;
        }

        public bool Login(string id, string pwd)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    cmd.Connection = this.sqlConnection;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Login";
                    SqlParameter p_id = new SqlParameter();
                    p_id.ParameterName = "@id";
                    p_id.Direction = ParameterDirection.Input;
                    p_id.SqlDbType = SqlDbType.Char;
                    p_id.Value = id.ToCharArray();

                    SqlParameter p_pwd = new SqlParameter();
                    p_pwd.ParameterName = "@pwd";
                    p_pwd.Direction = ParameterDirection.Input;
                    p_pwd.SqlDbType = SqlDbType.VarChar;
                    p_pwd.Value = pwd;

                    SqlParameter p_return = new SqlParameter();
                    p_return.SqlDbType = SqlDbType.Int;
                    p_return.ParameterName = "@return_value";
                    p_return.Direction = ParameterDirection.ReturnValue;

                    cmd.Parameters.AddRange(new SqlParameter[] { p_id, p_pwd,p_return });


                    if (this.sqlConnection.State != ConnectionState.Open)
                    {
                        this.sqlConnection.Open();
                    }

                    cmd.ExecuteNonQuery();
                    return ((int)p_return.Value) == 1 ? true : false;
                }
                catch (Exception e)           
                {

                    throw e;
                }
                
            }

        }

        public UserInfor GetUserInfor(string User_ID)
        {
            UserInfor ui = new UserInfor();

            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    cmd.Connection = this.sqlConnection;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "getUserInfor";

                    cmd.Parameters.Add(new SqlParameter("@User_ID", User_ID.ToCharArray()));

                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();

                    sda.Fill(dt);

                    ui.ID = dt.Rows[0]["ID"].ToString();
                    ui.Name = dt.Rows[0]["Name"].ToString();
                    ui.Channel_ID = dt.Rows[0]["Channel_ID"].ToString();
                    ui.Channel_Name = dt.Rows[0]["Channel_Name"].ToString();
                    string _auths=dt.Rows[0]["Auths"].ToString();
                    if (_auths.Contains('*'))
                        ui.Auths = _auths.Split('*');
                    else
                    {
                        ui.Auths = new string[] { _auths };
                    }

                    return ui;
                }
                catch (Exception e)
                {
                    
                    throw e;
                }

            }
        }


        public DataTable GetTaskInfor(string User_ID)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    cmd.Connection = this.sqlConnection;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "getUserInfor";
                    SqlParameter p_id = new SqlParameter();
                    p_id.ParameterName = "@User_ID";
                    p_id.Direction = ParameterDirection.Input;
                    p_id.SqlDbType = SqlDbType.Char;
                    p_id.Value = User_ID.ToCharArray();


                    cmd.Parameters.Add(p_id);

                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable("TaskInfor");

                    sda.Fill(dt);

                    return dt;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
           
        }


        public DataTable GetTableInfor(string Table_Name)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    cmd.Connection = this.sqlConnection;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "getTaskTable";
                    SqlParameter p_id = new SqlParameter();
                    p_id.ParameterName = "@Task_Name";
                    p_id.Direction = ParameterDirection.Input;
                    p_id.SqlDbType = SqlDbType.Char;
                    p_id.Value = Table_Name.ToCharArray();


                    cmd.Parameters.Add(p_id);

                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable("TableInfor");

                    sda.Fill(dt);

                    return dt;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
           
        }
    }
}
